/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.session;

import com.kallsonys.object.ElementQuery;
import com.kallsonys.object.Product;
import com.kallsonys.object.Vrankingcategory;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author allan
 */
@Stateless
public class ProductFacade extends AbstractFacade<Product> {

    @PersistenceContext(unitName = "Kallsonys-WarPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public ProductFacade() {
        super(Product.class);
    }

    public List<Product> getById(String idProduct) {
        List<Product> products = new ArrayList<Product>();
        if (idProduct != null && idProduct.length() > 0) {
            Query queryfindByProductidproduct = em.createNamedQuery("Product.findByProductidproduct");
            queryfindByProductidproduct.setParameter("productidproduct", Integer.valueOf(idProduct));
            products = (List<Product>) queryfindByProductidproduct.getResultList();
        }

        return products;
    }

    public Product getByIdItem(String idProduct) {
        Product product = new Product();
        if (idProduct != null && idProduct.length() > 0) {
            Query queryfindByProductidproduct = em.createNamedQuery("Product.findByProductidproduct");
            queryfindByProductidproduct.setParameter("productidproduct", Integer.valueOf(idProduct));
            product = (Product) queryfindByProductidproduct.getSingleResult();
        }

        return product;
    }

    public List<Product> getByName(String name) {
        List<Product> products = new ArrayList<Product>();
        if (name != null && name.length() > 0) {
            Query queryfindByProductidproduct = em.createQuery("SELECT p FROM Product p WHERE p.productname like :productname");
            queryfindByProductidproduct.setParameter("productname", "%" + name.toUpperCase() + "%");
            products = (List<Product>) queryfindByProductidproduct.getResultList();
        }

        return products;
    }

    public ElementQuery<Product> getByName(String name, int page) {
        ElementQuery<Product> objReturn = new ElementQuery<Product>();
        List<Product> products = new ArrayList<Product>();
        int total = 0;

        if (name != null && name.length() > 0) {
            Query queryfindByProductidproduct = em.createQuery("SELECT p FROM Product p WHERE p.productname like :productname");
            queryfindByProductidproduct.setParameter("productname", "%" + name.toUpperCase() + "%");

            int[] rango = {page * 10, (page * 10 + 10)};

            products = (List<Product>) this.findRange(rango, queryfindByProductidproduct);


            queryfindByProductidproduct = em.createQuery("SELECT Count(8) FROM Product p WHERE p.productname like :productname");
            queryfindByProductidproduct.setParameter("productname", "%" + name.toUpperCase() + "%");

            total = ((Long) queryfindByProductidproduct.getSingleResult()).intValue();
//            if (products.size() > 0)
//                total =  this.count(queryfindByProductidproduct);
        }
        objReturn.setLstItems(products);
        objReturn.setTotalPages(total);

        return objReturn;
    }

    public List<Product> getByDescription(String name) {
        List<Product> products = new ArrayList<Product>();
        if (name != null && name.length() > 0) {
            Query queryfindByProductidproduct = em.createQuery("SELECT p FROM Product p WHERE p.productdescription like :productdescription");
            queryfindByProductidproduct.setParameter("productdescription", "%" + name.toUpperCase() + "%");
            products = (List<Product>) queryfindByProductidproduct.getResultList();
        }

        return products;
    }

    /**
     * getByDescription
     * @param name
     * @param page
     * @return 
     */
    public ElementQuery<Product> getByDescription(String name, int page) {
        ElementQuery<Product> objReturn = new ElementQuery<Product>();
        List<Product> products = new ArrayList<Product>();
        int total = 0;

        if (name != null && name.length() > 0) {
            Query queryfindByProductidproduct = em.createQuery("SELECT p FROM Product p WHERE p.productdescription like :productdescription");
            queryfindByProductidproduct.setParameter("productdescription", "%" + name.toUpperCase() + "%");

            int[] rango = {page * 10, (page * 10 + 10)};

            products = (List<Product>) this.findRange(rango, queryfindByProductidproduct);


            queryfindByProductidproduct = em.createQuery("SELECT Count(8) FROM Product p WHERE p.productdescription like :productdescription");
            queryfindByProductidproduct.setParameter("productdescription", "%" + name.toUpperCase() + "%");

            total = ((Long) queryfindByProductidproduct.getSingleResult()).intValue();


        }
        objReturn.setLstItems(products);
        objReturn.setTotalPages(total);

        return objReturn;
    }

    /**
     * Obtiene la Consulta del Ranking
     * @param startDate
     * @param endDate
     * @return List<Product>
     */
    public List<Product> getByRanking(String startDate, String endDate) {
        List<Product> products = new ArrayList<Product>();

        if (startDate != null && startDate.length() > 0 && endDate != null && endDate.length() > 0) {
            Query query = em.createNativeQuery(QueryRanking(startDate, endDate), Product.class);
            products = query.getResultList();
        }

        return products;
    }

    /**
     * getByCategory
     * @param startDate
     * @param endDate
     * @return 
     */
    public List<Vrankingcategory> getByCategory(String startDate, String endDate) {
        List<Vrankingcategory> products = new ArrayList<Vrankingcategory>();

        if (startDate != null && startDate.length() > 0 && endDate != null && endDate.length() > 0) {
            Query query = em.createNativeQuery(QueryCategory(startDate, endDate), Vrankingcategory.class);
            products = query.getResultList();
        }

        return products;
    }

    /**
     * getByIdCategory
     * @param category
     * @param page
     * @return 
     */
    public ElementQuery<Product> getByIdCategory(String category, int page) {
        ElementQuery<Product> objReturn = new ElementQuery<Product>();
        List<Product> products = new ArrayList<Product>();
        int total = 0;

        if (category != null && category.length() > 0) {
            Query queryfindByProductidproduct = em.createNamedQuery("Product.findByProductcategory");
            queryfindByProductidproduct.setParameter("productcategory", category);

            int[] rango = {page * 10, (page * 10 + 10)};

            products = (List<Product>) this.findRange(rango, queryfindByProductidproduct);


            queryfindByProductidproduct = em.createQuery("SELECT Count(8) FROM Product p WHERE p.productcategory = :productcategory");
            queryfindByProductidproduct.setParameter("productcategory", category);

            total = ((Long) queryfindByProductidproduct.getSingleResult()).intValue();
        }
        objReturn.setLstItems(products);
        objReturn.setTotalPages(total);

        return objReturn;
    }

    private static String QueryCategory(String startDate, String endDate) {
        String SQL = "    SELECT  "
                + "   ranking.rnum idRanking, "
                + "  ranking.total Total, "
                + "   ranking.Productcategory category "
                + "    FROM "
                + "     ( "
                + "        SELECT temp.Total, c.categorydescription Productcategory, rownum rnum "
                + "        FROM "
                + "        ( "
                + "           SELECT "
                + "               COUNT(1) Total, "
                + "               Product.Productcategory "
                + "           FROM  "
                + "               Orderitem "
                + "              JOIN Product ON (Orderitem.Productid = Product.Productid) "
                + "               JOIN Order_Oms ON (Order_Oms.Orderid = Orderitem.Orderid) "
                + "           WHERE    trunc(Order_Oms.Orderdate) >= trunc(to_date('" + startDate + "','yyyy/mm/dd')) "
                + "           AND      trunc(Order_Oms.Orderdate) <= trunc(to_date('" + endDate + "','yyyy/mm/dd')) "
                + "           GROUP BY Product.Productcategory "
                + "           order by count(1) desc "
                + "       ) temp "
                + "      inner join caregory c on (temp.productcategory = c.categoryid) "
                + "  ) ranking  "
                + "   where rnum <= 50 ";

        //System.out.println(SQL);

        return SQL;
    }

    
    private static String QueryRanking(String startDate, String endDate) {
        String SQL =
                " SELECT     P.PRODUCTID,      P.PRODUCTIDPRODUCT,     P.PRODUCTNAME,     P.PRODUCTDESCRIPTION,     C.CATEGORYDESCRIPTION PRODUCTCATEGORY, "
                + " P.PRODUCTLIST_PRICE,     NULL AS PRODUCTIMAGE,     P.PRODUCTIMAGEURL,     P.PRODUCTENABLE, "
                + "  TO_CHAR(Rankin.Total) PRODUCTPRODUCER FROM PRODUCT P INNER JOIN (     select temp.*, rownum rnum "
                + "  from  "
                + "  ( "
                + "      select   count(1) Total,   Orderitem.Productid Productid        "
                + "      from Order_Oms                "
                + "      join Orderitem on (Order_Oms.Orderid = Orderitem.Orderid) "
                + "      where "
                + "          trunc(Order_Oms.Orderdate) >= trunc(to_date('" + startDate + "','yyyy/mm/dd'))     "
                + "          and trunc(Order_Oms.Orderdate) <= trunc(to_date('" + endDate + "','yyyy/mm/dd')) "
                + "    group by Orderitem.Productid "
                + "     order by count(1) desc "
                + " ) temp "
                + " ) Rankin  ON (Rankin.rnum <= 10 and P.Productid = Rankin.Productid) "
                + " INNER JOIN CAREGORY C ON (P.PRODUCTCATEGORY = C.CATEGORYID)  ";
        return SQL;
    }
}
